package com.rybbaby.tss.front.tssStatistics.dao.imp;

import com.rybbaby.tss.core.bean.PageBean;
import com.rybbaby.tss.core.utils.BeanUtils;
import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssStatistics.dao.IViewStudentAttendanceDao;
import com.rybbaby.tss.front.tssStatistics.entity.ViewStudentAttendance;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description 员工表数据库操作实现
 * 
 * @author 雷春琼
 * 
 * @createDate 2017-08-10 上午12:25:24
 */
@Repository
public class ViewStudentAttendanceDaoImp extends GenericDao<ViewStudentAttendance> implements IViewStudentAttendanceDao {


    @Override
    public PageBean pageList(HashMap<String, Object> paramHashMap, int pageNo, int pageSize) throws InstantiationException, IllegalAccessException {


        String id = (String)paramHashMap.get("id");
        String adminUserId = (String)paramHashMap.get("adminUserId");
        String type = (String)paramHashMap.get("type");
        String nickName = (String)paramHashMap.get("nickName");
        String contactPhone = (String)paramHashMap.get("contactPhone");
        String maxUpdateTime_begin = (String)paramHashMap.get("maxUpdateTime_begin");
        String maxUpdateTime_end = (String)paramHashMap.get("maxUpdateTime_end");
        String startTime_begin = (String)paramHashMap.get("startTime_begin");
        String endTime_end = (String)paramHashMap.get("endTime_end");

        String idSql = "";
        String adminUserIdSql = "";
        String nickNameSql = "";
        String contactPhoneSql = "";
        String maxUpdateTime_beginSql = "";
        String maxUpdateTime_endSql = "";
        String startTime_beginSql = "";
        String endTime_endSql = "";
        if(id!=null && !id.equals("")){
            idSql = "AND tss_student.id = '"+id+"'\n";
        }
        if(adminUserId!=null && !adminUserId.equals("")){
            adminUserIdSql = "\tAND tss_student.adminUserId in ('"+adminUserId+"')\n";
        }
        if(nickName!=null && !nickName.equals("")){
            nickNameSql = "AND tss_student.nickName LIKE '%"+nickName+"%'\n";
        }
        if(id!=null && !id.equals("")){
            contactPhoneSql = "AND tss_student.contactPhone LIKE '%"+contactPhone+"%'\n";
        }
        if(maxUpdateTime_begin!=null && !maxUpdateTime_begin.equals("")){
            maxUpdateTime_beginSql = "\tAND unix_timestamp(maxUpdateTime) >= unix_timestamp('"+maxUpdateTime_begin+"')\n";
        }
        if(maxUpdateTime_end!=null && !maxUpdateTime_end.equals("")){
            maxUpdateTime_endSql = "\tAND unix_timestamp(maxUpdateTime) <= unix_timestamp('"+maxUpdateTime_end+"')\n";
        }
        if(startTime_begin!=null && !startTime_begin.equals("")){
            startTime_beginSql = "\tAND unix_timestamp(tss_class_lessons.classTime) >= unix_timestamp('"+startTime_begin+"')\n";
        }
        if(endTime_end!=null && !endTime_end.equals("")){
            endTime_endSql = "\tAND unix_timestamp(tss_class_lessons.classTime) <= unix_timestamp('"+endTime_end+"')\n";
        }

        String sql = "SELECT\n" +
                "\tE.*,\n" +
                "\tIFNULL(D.sumReported,0) AS sumReported,\n" +
                "\tIFNULL(D.sumAttendance,0) AS sumAttendance,\n" +
                "\tIFNULL(D.sumAbsence,0) AS sumAbsence,\n" +
                "\tIFNULL(D.sumMakeUpClass,0) AS sumMakeUpClass,\n" +
                "\tD.maxUpdateTime AS maxUpdateTime" +
                "\tFROM(\n" +
                "SELECT\n" +
                "\ttss_student.id,\n" +
                "\ttss_student.`name` AS studentName,\n" +
                "\ttss_student.birthdayDate,\n" +
                "\ttss_student.contactPhone\n" +
                "FROM\n" +
                "\ttss_student\n" +
                "\tWHERE \n" +
                "\t\t1=1\n" +
                adminUserIdSql +
                idSql +
                nickNameSql +
                contactPhoneSql +
                ")E\n" +
                "LEFT JOIN\n" +
                "(" +
                "SELECT \n" +
                "\tC.*\n" +
                "FROM(\n" +
                "SELECT\n" +
                "\tA.*,\n" +
                "\tcount(if(B.attendenceStatus=1,true,null)) AS sumAttendance,\n" +
                "\tcount(if(B.attendenceStatus=2,true,null)) AS sumAbsence,\n" +
                "\tcount(if(B.attendenceStatus=3,true,null)) AS sumMakeUpClass,\n" +
                "\tmax(B.updateTime) AS maxUpdateTime\n" +
                "FROM(\n" +
                "SELECT\n" +
                "\ttss_student.id,\n" +
                "\ttss_student.`name` AS studentName,\n" +
                "\ttss_student.birthdayDate,\n" +
                "\ttss_student.contactPhone,\n" +
                "\tCOUNT(C.id) AS sumReported\n" +
                "FROM\n" +
                "\ttss_student \n" +
                "LEFT JOIN tss_member_package \n" +
                "ON tss_student.id = tss_member_package.memberId\n" +
                "LEFT JOIN(\n" +
                "\tSELECT \n" +
                "\t\ttss_member_attend_class_record.* \n" +
                "\tFROM\n" +
                "\ttss_member_attend_class_record\n" +
                "\tLEFT JOIN tss_class_lessons ON tss_class_lessons.id = tss_member_attend_class_record.lessonsId\n" +
                "\tWHERE \n" +
                "\t\t1=1\n" +
                "\n" +
                startTime_beginSql +
                endTime_endSql +
                ")C\n" +
                "ON tss_member_package.id = C.memberPackageId\n" +
                "WHERE tss_member_package.type = "+type+"\n" +
                idSql +
                nickNameSql +
                contactPhoneSql +
                "GROUP BY tss_student.id\n" +
                ")A\n" +
                "LEFT JOIN(\n" +
                "SELECT\n" +
                "\ttss_member_check_attendance.*\n" +
                "FROM\n" +
                "\ttss_member_check_attendance\n" +
                "LEFT JOIN tss_member_attend_class_record ON tss_member_check_attendance.attendClassId = tss_member_attend_class_record.id\n" +
                "LEFT JOIN tss_class_lessons ON tss_member_attend_class_record.lessonsId = tss_class_lessons.id\n" +
                "WHERE\n" +
                "\ttss_member_check_attendance.type = "+type+"\n" +
                "\tAND tss_class_lessons.`status` = 1\n" +
                startTime_beginSql +
                endTime_endSql +
                ")B\n" +
                "ON A.id = B.memberId\n" +
                "GROUP BY A.id\n" +
                ")C\n" +
                "where 1=1\n" +
                maxUpdateTime_beginSql +
                maxUpdateTime_endSql +
                "ORDER BY maxUpdateTime" +
                ")D\n" +
                "ON E.id = D.id" +
                "\tWHERE \n" +
                "\t\t1=1\n" +
                maxUpdateTime_beginSql +
                maxUpdateTime_endSql ;
        System.out.println(sql);
        PageBean pageBean = this.pageList(sql, pageNo, pageSize);
        List<ViewStudentAttendance> populate = BeanUtils.populate(ViewStudentAttendance.class, (List<Map<String, Object>>) pageBean.getList(), "attendanceRate");
        pageBean.setList(populate);
        return pageBean;
    }
}
